package edu.base.dao;

import edu.base.model.CatDetail;
import edu.base.model.Statistics;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;

public interface CatDetailMapper {

	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@Delete({ "delete from cat_detail", "where detail_id = #{detailId,jdbcType=INTEGER}" })
	int deleteByPrimaryKey(Integer detailId);



	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@Insert({ "insert into cat_detail (detail_id, from_type, ", "to_id, cat_id, state, ", "in_date, out_date, ",
			"in_price, out_price, ", "detail_name, expire_time)",
			"values (#{detailId,jdbcType=INTEGER}, #{fromType,jdbcType=VARCHAR}, ",
			"#{toId,jdbcType=INTEGER}, #{catId,jdbcType=INTEGER}, #{state,jdbcType=INTEGER}, ",
			"#{inDate,jdbcType=TIMESTAMP}, #{outDate,jdbcType=TIMESTAMP}, ",
			"#{inPrice,jdbcType=DECIMAL}, #{outPrice,jdbcType=DECIMAL}, ",
			"#{detailName,jdbcType=VARCHAR}, #{expireTime,jdbcType=TIMESTAMP})" })
	int insert(CatDetail record);



	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@InsertProvider(type = CatDetailSqlProvider.class, method = "insertSelective")
	int insertSelective(CatDetail record);



	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@Select({ "select", "detail_id, from_type, to_id, cat_id, state, in_date, out_date, in_price, out_price, ",
			"detail_name, expire_time", "from cat_detail", "where detail_id = #{detailId,jdbcType=INTEGER}" })
	@Results({ @Result(column = "detail_id", property = "detailId", jdbcType = JdbcType.INTEGER, id = true),
			@Result(column = "from_type", property = "fromType", jdbcType = JdbcType.VARCHAR),
			@Result(column = "to_id", property = "toId", jdbcType = JdbcType.INTEGER),
			@Result(column = "cat_id", property = "catId", jdbcType = JdbcType.INTEGER),
			@Result(column = "state", property = "state", jdbcType = JdbcType.INTEGER),
			@Result(column = "in_date", property = "inDate", jdbcType = JdbcType.TIMESTAMP),
			@Result(column = "out_date", property = "outDate", jdbcType = JdbcType.TIMESTAMP),
			@Result(column = "in_price", property = "inPrice", jdbcType = JdbcType.DECIMAL),
			@Result(column = "out_price", property = "outPrice", jdbcType = JdbcType.DECIMAL),
			@Result(column = "detail_name", property = "detailName", jdbcType = JdbcType.VARCHAR),
			@Result(column = "expire_time", property = "expireTime", jdbcType = JdbcType.TIMESTAMP) })
	CatDetail selectByPrimaryKey(Integer detailId);



	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@UpdateProvider(type = CatDetailSqlProvider.class, method = "updateByPrimaryKeySelective")
	int updateByPrimaryKeySelective(CatDetail record);



	/**
	 * This method was generated by MyBatis Generator. This method corresponds to the database table cat_detail
	 * @mbg.generated  Thu Apr 02 21:12:10 CST 2020
	 */
	@Update({ "update cat_detail", "set from_type = #{fromType,jdbcType=VARCHAR},", "to_id = #{toId,jdbcType=INTEGER},",
			"cat_id = #{catId,jdbcType=INTEGER},", "state = #{state,jdbcType=INTEGER},",
			"in_date = #{inDate,jdbcType=TIMESTAMP},", "out_date = #{outDate,jdbcType=TIMESTAMP},",
			"in_price = #{inPrice,jdbcType=DECIMAL},", "out_price = #{outPrice,jdbcType=DECIMAL},",
			"detail_name = #{detailName,jdbcType=VARCHAR},", "expire_time = #{expireTime,jdbcType=TIMESTAMP}",
			"where detail_id = #{detailId,jdbcType=INTEGER}" })
	int updateByPrimaryKey(CatDetail record);



	@Select({ 
		"<script>",
		"select", 
		"*",
		"from cat_detail",  
		"where 1=1",
		"<if test='catId != null'>",
		"and cat_id=#{catId, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='state != null'>",
		"and state=#{state, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='guoqi'>",
		"and expire_time &lt; now()",
		"</if>",
		
		"<if test='toId != null'>",
		"and to_id=#{toId, jdbcType=INTEGER}",
		"</if>",
		
		"</script>",
	})
	@Results({ @Result(column = "detail_id", property = "detailId", jdbcType = JdbcType.INTEGER, id = true),
		@Result(column = "from_type", property = "fromType", jdbcType = JdbcType.VARCHAR),
		@Result(column = "to_id", property = "toId", jdbcType = JdbcType.INTEGER),
		@Result(column = "cat_id", property = "catId", jdbcType = JdbcType.INTEGER),
		@Result(column = "state", property = "state", jdbcType = JdbcType.INTEGER),
		@Result(column = "in_date", property = "inDate", jdbcType = JdbcType.TIMESTAMP),
		@Result(column = "out_date", property = "outDate", jdbcType = JdbcType.TIMESTAMP),
		@Result(column = "in_price", property = "inPrice", jdbcType = JdbcType.DECIMAL),
		@Result(column = "out_price", property = "outPrice", jdbcType = JdbcType.DECIMAL),
		@Result(column = "detail_name", property = "detailName", jdbcType = JdbcType.VARCHAR),
		@Result(column = "expire_time", property = "expireTime", jdbcType = JdbcType.TIMESTAMP) 
	})
	List<CatDetail> query(@Param("catId") Integer catId, @Param("state") Integer state,@Param("guoqi") Boolean guoqi, @Param("toId") Integer toId);



	@Select({ 
		"<script>",
		"select", 
		"count(*)",
		"from cat_detail",  
		"where 1=1",
		"<if test='catId != null'>",
		"and cat_id=#{catId, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='state != null'>",
		"and state=#{state, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='guoqi'>",
		"and expire_time &lt; now()",
		"</if>",
		
		"<if test='toId != null'>",
		"and to_id=#{toId, jdbcType=INTEGER}",
		"</if>",
		
		"</script>",
	})
	Integer count(@Param("catId") Integer catId, @Param("state") Integer state,@Param("guoqi") Boolean guoqi, @Param("toId") Integer toId);

	@Select({ 
		"<script>",
		"select", 
		"*",
		"from cat_detail",  
		"where 1=1",
		
		"<if test='formType != null'>",
		"and from_type like #{formType,jdbcType=VARCHAR}",
		"</if>",
	
		
		"<if test='catId != null'>",
		"and cat_id=#{catId, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='state != null'>",
		"and state=#{state, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='guoqi'>",
		"and expire_time &lt; now()",
		"</if>",
		
		"<if test='toId != null'>",
		"and to_id=#{toId, jdbcType=INTEGER}",
		"</if>",
		"limit #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}",
		"</script>",
	})
	@Results({ @Result(column = "detail_id", property = "detailId", jdbcType = JdbcType.INTEGER, id = true),
		@Result(column = "from_type", property = "fromType", jdbcType = JdbcType.VARCHAR),
		@Result(column = "to_id", property = "toId", jdbcType = JdbcType.INTEGER),
		@Result(column = "cat_id", property = "catId", jdbcType = JdbcType.INTEGER),
		@Result(column = "state", property = "state", jdbcType = JdbcType.INTEGER),
		@Result(column = "in_date", property = "inDate", jdbcType = JdbcType.TIMESTAMP),
		@Result(column = "out_date", property = "outDate", jdbcType = JdbcType.TIMESTAMP),
		@Result(column = "in_price", property = "inPrice", jdbcType = JdbcType.DECIMAL),
		@Result(column = "out_price", property = "outPrice", jdbcType = JdbcType.DECIMAL),
		@Result(column = "detail_name", property = "detailName", jdbcType = JdbcType.VARCHAR),
		@Result(column = "expire_time", property = "expireTime", jdbcType = JdbcType.TIMESTAMP) 
	})
	List<CatDetail> query2(@Param("formType") String formType,@Param("catId") Integer catId, @Param("state") Integer state,@Param("guoqi") Boolean guoqi, @Param("toId") Integer toId, @Param("start") Integer start, @Param("size") Integer size);

	
	@Select({ 
		"<script>",
		"select", 
		"count(*)",
		"from cat_detail",  
		"where 1=1",
		
		"<if test='formType != null'>",
		"and from_type like #{formType,jdbcType=VARCHAR}",
		"</if>",
	
		
		"<if test='catId != null'>",
		"and cat_id=#{catId, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='state != null'>",
		"and state=#{state, jdbcType=INTEGER}",
		"</if>",
		
		"<if test='guoqi'>",
		"and expire_time &lt; now()",
		"</if>",
		
		"<if test='toId != null'>",
		"and to_id=#{toId, jdbcType=INTEGER}",
		"</if>",
		"</script>",
	})
	Integer count2(@Param("formType") String formType,@Param("catId") Integer catId, @Param("state") Integer state,@Param("guoqi") Boolean guoqi, @Param("toId") Integer toId);

	
	
	@Select({
		"select year(in_date) as y,count(*) as c, from_type as n",
		"from cat_detail",
		"where year(in_date) = #{yy, jdbcType=INTEGER}",
		"group by from_type,y"
	})
	@Results({ 
		@Result(column = "c", property = "c", jdbcType = JdbcType.INTEGER),
		@Result(column = "n", property = "n", jdbcType = JdbcType.VARCHAR)
	})
	List<Statistics> stat(@Param("yy") Integer year);

}